rm(list=ls())
gc()
library(data.table)
library(modelsummary)
library(stringr)
library(dplyr)
library(xtable)
library(tibble)
library(tidyr)
library(gt)
library(readr)




l=rbindlist(lapply(c(2016,2012,2008), FUN = function(y1){
  
if(y1==2012){
  y2=2016
} else if(y1==2016){
  y2=2020
} else if(y1==2008){
  y2=2012
}
  s=fread(paste0('linked-unlinked-',y1,'-',y2,'.csv.gz'))
  
 
  
  s[,Female:=as.numeric(toupper(Gender)=='FEMALE')]
  s[,White:=as.numeric(Race=='White')]
  s[,Black:=as.numeric(Race=='Black')]
  s[,Hispanic:=as.numeric(Race=='Hispanic')]
  s[,Asian:=as.numeric(Race=='Asian')]
s[,Dem:=as.numeric(Party=='Democrat')]
s[,Rep:=as.numeric(Party=='Republican')]


 means= s[,list(
   Age = mean(Age_year1,na.rm=T),
   
      Democrat = mean(Dem,na.rm=T),
      Republican = mean(Rep,na.rm=T),
      White = mean(White,na.rm=T),
      Black = mean(Black,na.rm=T),
      Hispanic=mean(Hispanic,na.rm=T),
      Asian = mean(Asian, na.rm=T),
      Female = mean(Female,na.rm=T),
     # `Vote General`= mean(GenVote,na.rm=T),
    #  `Vote Primary`= mean(PrimVote,na.rm=T),
      
      `Block Group Democrat` = mean(DemBlockGroup,na.rm=T),
      `Block Group Republican` = mean(RepBlockGroup,na.rm=T),
      `Block Group White` = mean(WhiteBlockGroup,na.rm=T),
      `Block Group Registered` = mean(RegBlockGroup,na.rm=T),
      `Block Group Median Age`=mean(MedianAgeBlockGroup,na.rm=T),
       `Block Group Median Household Income` = mean(HHIncomeBlockGroup,na.rm=T),
      `Block Group Median Year House Built` = mean(YearBuiltBlockGroup,na.rm=T),
      `Block Group Median House Value` = mean(HouseValueBlockGroup,na.rm=T),
      `Block Group Homeowner` = mean(HomeownerBlockGroup,na.rm=T),
       `Block Group Drive to Work` = mean(DriveWorkBlockGroup,na.rm=T),
      `Democratic Exposure` = mean(DemSpExp_nohh_year1,na.rm=T),
      `Republican Exposure` = mean(RepSpExp_nohh_year1,na.rm=T)

      
      
      
    ) ,by='Sample'][,Years:=paste0(y1,'-',y2)]
    return(means)
}),fill=T)




##  Table
t = as_tibble(l)%>%
  pivot_longer(Age:`Republican Exposure`)%>%
  filter(Years=='2008-2012' & Sample=='Linked')%>%
  mutate(Linked = value,
         Variable = name)%>%
select(Variable,Linked)  %>%
  left_join(
    as_tibble(l)%>%
      pivot_longer(Age:`Republican Exposure`)%>%
      filter(Years=='2008-2012' & Sample=='Unlinked')%>%
      mutate(Unlinked = value,
             Variable = name)%>%
  select(Variable,Unlinked)
    
    ) %>%
  left_join( as_tibble(l)%>%
               pivot_longer(Age:`Republican Exposure`)%>%
               filter(Years=='2012-2016' & Sample=='Linked')%>%
               mutate(Linked = value,
                      Variable = name)%>%
               select(Variable,Linked)  %>%
               left_join(
                 as_tibble(l)%>%
                   pivot_longer(Age:`Republican Exposure`)%>%
                   filter(Years=='2012-2016' & Sample=='Unlinked')%>%
                   mutate(Unlinked = value,
                          Variable = name)%>%
                   select(Variable,Unlinked)
                 
               ),by='Variable')%>%
  left_join( as_tibble(l)%>%
               pivot_longer(Age:`Republican Exposure`)%>%
               filter(Years=='2016-2020' & Sample=='Linked')%>%
               mutate(Linked = value,
                      Variable = name)%>%
               select(Variable,Linked)  %>%
               left_join(
                 as_tibble(l)%>%
                   pivot_longer(Age:`Republican Exposure`)%>%
                   filter(Years=='2016-2020' & Sample=='Unlinked')%>%
                   mutate(Unlinked = value,
                          Variable = name)%>%
                   select(Variable,Unlinked)
                 
               ),by='Variable')
  

out = t %>%
  gt()%>%
  fmt_number(columns = 2:7, decimals = 3)%>%
  cols_label(
    `Linked.x` = 'Linked',
    `Unlinked.x` = 'Unlinked',
    `Linked.y` = 'Linked',
    `Unlinked.y` = 'Unlinked',
    )%>%
  tab_spanner(label = '2008-2012',columns = 2:3)%>%
  tab_spanner(label = '2012-2016',columns = 4:5)%>%
  tab_spanner(label = '2016-2020',columns = 6:7)%>%

  as_latex()%>%
  as.character()%>%
  str_replace_all('longtable','tabular')

write_file(out, 'tables/TabS2.tex')
  

 
